import pandas as pd
import numpy as np


def excel_to_config(excel_path, output_path='config.properties'):
    """
    将Excel数据转换为配置文件格式

    Args:
        excel_path: Excel文件路径
        output_path: 输出配置文件路径

    Returns:
        config_lines: 生成的配置行列表
    """

    try:
        # 读取Excel文件
        print("正在读取Excel文件...")
        df = pd.read_excel(excel_path)

        print(f"Excel文件形状: {df.shape}")
        print(f"列名: {list(df.columns)}")

        # 检查必要的列是否存在
        required_cols = ['学科id', '教材名称', '基础目录教材id', 'tutorial_version_id']
        missing_cols = [col for col in required_cols if col not in df.columns]

        if missing_cols:
            raise ValueError(f"Excel文件缺少必要的列: {missing_cols}")

        # 过滤tutorial_version_id为空的数据
        original_count = len(df)
        df_filtered = df[df['tutorial_version_id'].notna() & (df['tutorial_version_id'] != '')].copy()
        filtered_count = len(df_filtered)

        print(f"\n数据过滤结果:")
        print(f"原始记录数: {original_count}")
        print(f"过滤后记录数: {filtered_count}")
        print(f"过滤掉的记录数: {original_count - filtered_count}")

        if filtered_count == 0:
            print("警告: 过滤后没有有效数据!")
            return []

        # 生成配置行
        config_lines = []

        for index, row in df_filtered.iterrows():
            # 获取数据值
            subject_id = str(int(row['学科id'])) if pd.notna(row['学科id']) else ''
            base_edition_id = str(int(row['基础目录教材id'])) if pd.notna(row['基础目录教材id']) else ''
            tutorial_version_id = str(int(row['tutorial_version_id'])) if pd.notna(row['tutorial_version_id']) else ''

            # 生成三行配置
            config_lines.extend([
                f"istudy.edition.mapping-config[{index}].base-edition-id={base_edition_id}",
                f"istudy.edition.mapping-config[{index}].tutorial-version-id={tutorial_version_id}",
                f"istudy.edition.mapping-config[{index}].subject-id={subject_id}"
            ])

        # 保存到文件
        with open(output_path, 'w', encoding='utf-8') as f:
            for line in config_lines:
                f.write(line + '\n')

        print(f"\n配置文件已生成: {output_path}")
        print(f"总配置行数: {len(config_lines)}")
        print(f"配置组数: {len(config_lines) // 3}")

        # 显示前几行配置作为示例
        print(f"\n前10行配置示例:")
        for line in config_lines[:10]:
            print(line)

        if len(config_lines) > 10:
            print("...")

        return config_lines

    except Exception as e:
        print(f"处理过程中出现错误: {e}")
        return []


def excel_to_config_with_sequential_index(excel_path, output_path='config_sequential.properties'):
    """
    使用连续索引生成配置（从0开始的连续索引，而不是使用Excel的原始行号）

    Args:
        excel_path: Excel文件路径
        output_path: 输出配置文件路径
    """

    try:
        # 读取Excel文件
        print("正在读取Excel文件...")
        df = pd.read_excel(excel_path)

        # 检查必要的列
        required_cols = ['学科id', '教材名称', '基础目录教材id', 'tutorial_version_id']
        missing_cols = [col for col in required_cols if col not in df.columns]

        if missing_cols:
            raise ValueError(f"Excel文件缺少必要的列: {missing_cols}")

        # 过滤数据
        df_filtered = df[df['tutorial_version_id'].notna() & (df['tutorial_version_id'] != '')].copy()

        print(f"过滤后有效记录数: {len(df_filtered)}")

        if len(df_filtered) == 0:
            print("警告: 过滤后没有有效数据!")
            return []

        # 重置索引，使用连续的索引
        df_filtered = df_filtered.reset_index(drop=True)

        config_lines = []

        for index, row in df_filtered.iterrows():
            # 确保数值为整数格式
            subject_id = str(int(row['学科id'])) if pd.notna(row['学科id']) else ''
            base_edition_id = str(int(row['基础目录教材id'])) if pd.notna(row['基础目录教材id']) else ''
            tutorial_version_id = str(int(row['tutorial_version_id'])) if pd.notna(row['tutorial_version_id']) else ''

            # 使用连续索引
            config_lines.extend([
                f"istudy.edition.mapping-config[{index}].base-edition-id={base_edition_id}",
                f"istudy.edition.mapping-config[{index}].tutorial-version-id={tutorial_version_id}",
                f"istudy.edition.mapping-config[{index}].subject-id={subject_id}"
            ])

        # 保存到文件
        with open(output_path, 'w', encoding='utf-8') as f:
            for line in config_lines:
                f.write(line + '\n')

        print(f"\n连续索引配置文件已生成: {output_path}")
        print(f"配置组数: {len(df_filtered)}")

        # 显示示例
        print(f"\n配置示例:")
        for line in config_lines[:9]:  # 显示前3组配置
            print(line)

        return config_lines

    except Exception as e:
        print(f"处理过程中出现错误: {e}")
        return []


def preview_excel_data(excel_path):
    """
    预览Excel数据，帮助确认数据格式

    Args:
        excel_path: Excel文件路径
    """
    try:
        df = pd.read_excel(excel_path)

        print("=== Excel数据预览 ===")
        print(f"总行数: {len(df)}")
        print(f"列名: {list(df.columns)}")

        print(f"\n前5行数据:")
        print(df.head())

        print(f"\n数据类型:")
        print(df.dtypes)

        # 检查tutorial_version_id列的情况
        if 'tutorial_version_id' in df.columns:
            null_count = df['tutorial_version_id'].isna().sum()
            empty_count = (df['tutorial_version_id'] == '').sum()
            valid_count = len(df) - null_count - empty_count

            print(f"\ntutorial_version_id列情况:")
            print(f"有效数据: {valid_count}")
            print(f"空值(NaN): {null_count}")
            print(f"空字符串: {empty_count}")

            if valid_count > 0:
                print(f"有效数据示例:")
                valid_data = df[df['tutorial_version_id'].notna() & (df['tutorial_version_id'] != '')]
                print(valid_data[['学科id', '教材名称', '基础目录教材id', 'tutorial_version_id']].head())

    except Exception as e:
        print(f"预览数据时出现错误: {e}")


def generate_config_with_validation(excel_path, output_path='validated_config.properties'):
    """
    带数据验证的配置生成

    Args:
        excel_path: Excel文件路径
        output_path: 输出配置文件路径
    """
    try:
        df = pd.read_excel(excel_path)

        # 数据验证和清理
        print("=== 数据验证 ===")

        # 检查必要列
        required_cols = ['学科id', '教材名称', '基础目录教材id', 'tutorial_version_id']
        for col in required_cols:
            if col not in df.columns:
                raise ValueError(f"缺少必要的列: {col}")

        # 过滤和验证数据
        df_clean = df.copy()

        # 过滤tutorial_version_id为空的行
        df_clean = df_clean[df_clean['tutorial_version_id'].notna() & (df_clean['tutorial_version_id'] != '')]

        # 确保ID列都是数字
        numeric_cols = ['学科id', '基础目录教材id', 'tutorial_version_id']
        for col in numeric_cols:
            # 尝试转换为数字，失败的行会被标记
            df_clean[col] = pd.to_numeric(df_clean[col], errors='coerce')

        # 删除任何包含NaN的行（转换失败的行）
        df_clean = df_clean.dropna(subset=numeric_cols)

        print(f"原始数据行数: {len(df)}")
        print(f"清理后数据行数: {len(df_clean)}")

        if len(df_clean) == 0:
            print("警告: 没有有效的数据行!")
            return []

        # 生成配置
        config_lines = []
        df_clean = df_clean.reset_index(drop=True)

        for index, row in df_clean.iterrows():
            subject_id = int(row['学科id'])
            base_edition_id = int(row['基础目录教材id'])
            tutorial_version_id = int(row['tutorial_version_id'])

            config_lines.extend([
                f"istudy.edition.mapping-config[{index}].base-edition-id={base_edition_id}",
                f"istudy.edition.mapping-config[{index}].tutorial-version-id={tutorial_version_id}",
                f"istudy.edition.mapping-config[{index}].subject-id={subject_id}"
            ])

        # 保存文件
        with open(output_path, 'w', encoding='utf-8') as f:
            for line in config_lines:
                f.write(line + '\n')

        print(f"\n验证后的配置文件已生成: {output_path}")
        print(f"有效配置组数: {len(df_clean)}")

        return config_lines

    except Exception as e:
        print(f"验证过程中出现错误: {e}")
        return []


# 使用示例
if __name__ == "__main__":
    # Excel文件路径 - 请修改为你的实际文件路径
    excel_file = "D:\\pycharmProject\\pythonProject\\测试-合并结果.xlsx"

    print("=== Excel转配置文件工具 ===")

    # 1. 预览数据
    print("1. 预览Excel数据...")
    # preview_excel_data(excel_file)

    # 2. 生成配置文件（使用连续索引）
    print("\n2. 生成配置文件...")
    config_lines = excel_to_config_with_sequential_index(excel_file, "edition_mapping_config.properties")

    # 3. 带验证的生成（推荐使用）
    print("\n3. 生成验证后的配置文件...")
    # validated_config = generate_config_with_validation(excel_file, "validated_edition_config.properties")


# 快速使用函数
def quick_generate_config(excel_file, output_file="config.properties"):
    """
    快速生成配置文件
    """
    return excel_to_config_with_sequential_index(excel_file, output_file)